package com.ce.framework.util;import java.io.IOException;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import oracle.jdbc.driver.OracleDriver;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

import com.ce.framework.datamaintain.logic.ISQLMapper;
import com.ce.util.DbUtil;
import com.ce.util.TreeNode;


public class MenuLibUtil {	
	/**
	 * 开发人： andy
	 * 开发时间： 2012-4-17 下午5:14:01
	 * 功能描述：
	 * 方法的参数和返回值
	 * @param args
	 * void 
	 * ==================================
	 * 修改历史
	 * 修改人        修改时间      修改原因及内容
	 *
	 * ==================================
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		Connection con = DbUtil.getConnection(OracleDriver.class, "jdbc:oracle:thin:@localhost:1521:csmisdb", "sczhcs", "csmis");
		loadConfFromDb(con,"sczh");
	}
	public static void temp(Connection con,String sql) throws IOException{
		try {
			Statement st = con.createStatement();
			st.executeUpdate(sql);
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void loadConfFromDb(Connection con,String rootId) throws IOException{
		DbUtil._CONNECTION = new UnClosedConnection(con);
		String sql = "select vc_id,vc_name,path,vc_url from (select t.vc_id,t.vc_name,t.vc_url,sys_connect_by_path(t.vc_name,'/') path,sys_connect_by_path(t.vc_id,'|') ids,t.int_seq "+
				"from sys_t_menu t start with vc_id='"+rootId+"' connect by vc_par$id=prior vc_id) where vc_url is not null order by length(ids),int_seq";
		List<TreeNode> list = (List<TreeNode>) DbUtil.executeSqlQuery(sql, TreeNode.mapper4);
		Document doc = DocumentHelper.createDocument();
		final Element root = doc.addElement("conf");
		for(TreeNode node:list){
			final TreeNode n = node;
			if(StringUtil.isBlank(n.getVcUserData())){
				continue;
			}
			sql = "select t.vc_name,t.vc_simple$name,t.vc_funname,t.vc_idname,t.vc_ico,t.n_seq from sys_t_menu$operations t where t.vc_menu$id='"+
					n.getVcId()+"' and t.vc_del$flag=0 order by t.n_seq";
			final Element e = root.addElement("menu");
			if(n.getVcParId()!=null){
				if(n.getVcParId().startsWith("/"))n.setVcParId(n.getVcParId().substring(1));
			}
			e.addAttribute("module", n.getVcParId());
			e.addAttribute("name", n.getVcName());
			e.addAttribute("url", n.getVcUserData());
			DbUtil.executeSqlQuery(sql, new ISQLMapper() {
				public Object fromSQLQuery(ResultSet rs, int num) {
					try {
						Element op = e.addElement("operation");
						op.addAttribute("seq", rs.getString("n_seq"));
						op.addAttribute("name", rs.getString("vc_name"));
						op.addAttribute("opName", rs.getString("vc_simple$name"));
						op.addAttribute("funcName", rs.getString("vc_funname"));
						op.addAttribute("btnName", rs.getString("vc_idname"));
						op.addAttribute("icon", rs.getString("vc_ico"));
						op.setText(rs.getString("vc_name"));
					} catch (SQLException e1) {
						LogUtil.logError(e1);
					}
					return null;
				}
			});
		}
		System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n");
        OutputFormat format = OutputFormat.createPrettyPrint();
        XMLWriter writer = new XMLWriter(System.out, format);
        writer.write(doc);
        writer.close();
	}
	
	public static void initMenuLib(){
		String sql = "select count(*) from user_tab_comments where table_name='SYS_T_MENU$LIB'";
		if(DbUtil.getFirstIntSqlQuery(sql)==0){//建表
			sql = "create table SYS_T_MENU$LIB(" +
					"VC_ID       VARCHAR2(50) default sys_guid() not null,"+
					"VC_NAME     VARCHAR2(50) not null,"+
					"VC_URL      VARCHAR2(500) not null,"+
					"VC_PATH     VARCHAR2(500) not null,"+
					"OP_SEQ   	 NUMBER,"+
					"OP_NAME     VARCHAR2(50),"+
					"OP_SNAME    VARCHAR2(50),"+
					"OP_FNAME    VARCHAR2(50),"+
					"OP_BNAME	 VARCHAR2(50),"+
					"OP_ICON	 VARCHAR2(50),"+
					"OP_NOTE	 VARCHAR2(4000)"+
					")";
			DbUtil.executeSqlUpdate(sql);
		}
        SAXReader reader = new SAXReader();
        try {
			Enumeration<URL> en = MenuLibUtil.class.getClassLoader().getResources("menu-lib.xml");
			while(en.hasMoreElements()){
				URL url = en.nextElement();
			    try {
		        	LogUtil.log=false;
					Document doc = reader.read(url.openStream());
					Element root = doc.getRootElement();
					Iterator<?> i = root.elementIterator("menu");
					while(i!=null&&i.hasNext()){
						Element menu = (Element) i.next();
						sql = "delete from SYS_T_MENU$LIB where vc_url=?";
						DbUtil.executeSqlUpdate(sql,new Object[]{menu.attributeValue("url")});
						Iterator<?> opi = menu.elementIterator("operation");
						while(opi!=null&&opi.hasNext()){
							Element op = (Element) opi.next();
							sql = "insert into SYS_T_MENU$LIB(vc_name,vc_url,vc_path,op_seq,op_name,op_sname,op_fname,op_bname,op_icon,op_note)" +
									"values(?,?,?,?,?,?,?,?,?,?)";
							DbUtil.executeSqlUpdate(sql,new Object[]{menu.attributeValue("name"),menu.attributeValue("url"),menu.attributeValue("module"),
									Integer.parseInt(op.attributeValue("seq")),op.attributeValue("name"),op.attributeValue("opName"),op.attributeValue("funcName"),
									op.attributeValue("btnName"),op.attributeValue("icon"),op.getText()});
						}
					}
				} catch (DocumentException e) {
		        	LogUtil.log=true;
					LogUtil.logError(e);
				} catch (IOException e) {
		        	LogUtil.log=true;
					LogUtil.logError(e);
				}
			}
		} catch (IOException e) {
        	LogUtil.log=true;
			LogUtil.logError(e);
		}
        finally{
        	LogUtil.log=true;
        }
	}
}

